﻿IF EXISTS (
    SELECT
      1
    FROM sysobjects
    WHERE id = OBJECT_ID('bd_proc_leave_query')
      AND type IN ('P', 'PC')
  )
  DROP PROCEDURE bd_proc_leave_query

GO


-- =============================================
-- Author:      YFC
-- Create date: 2020-07-28 20:22:22
-- Database:    [scm_main-hysj-test]
-- Description: 请假管理-请假列表记录查询
-- =============================================

CREATE PROCEDURE bd_proc_leave_query
  @dateBegin DATE,
  @dateEnd  DATE,
  @depts  VARCHAR(MAX) = '',    --提交人部门权限
  @userNameOrNo VARCHAR(100) = '',  --申请人工号或姓名
  @applierNameOrNo  VARCHAR(100) = '', --提交人工号或姓名
  @dateType  INT = 1,    --1：按出勤时间查询 2：按提交时间查询
  @orderNo VARCHAR(20) = '',
  @operateId  BIGINT,   --当前登录人
  @isPage  INT = 1,
  @pageSize  INT = 50,
  @pageNumber  INT = 1,
  @rowCount  INT OUTPUT
AS
BEGIN
  SET NOCOUNT ON;

  --补出勤单号、开始日期、结束日期、总人数、补出勤事由、审批状态、最新审批人、最新审批时间、驳回原因、提交人工号、提交人姓名、提交时间

  CREATE TABLE #result
  (
    order_id BIGINT 
    , order_no VARCHAR(20) COLLATE Chinese_PRC_CI_AS   --请假单号
    , start_date DATE --开始日期
    , end_date DATE     --结束日期
    , people_num INT   --总人数
    , leave_type_id VARCHAR(20)
    , leave_type_name VARCHAR(20)
    , remark VARCHAR(2000) COLLATE Chinese_PRC_CI_AS --请假事由
    , status  INT
    , status_name VARCHAR(20) COLLATE Chinese_PRC_CI_AS   --审批状态
    , last_approver_id  BIGINT
    , last_approver_name  VARCHAR(100) COLLATE Chinese_PRC_CI_AS    --最新审批人
    , last_approver_time  DATETIME  --最新审批时间
    , returnRemark VARCHAR(2000) COLLATE Chinese_PRC_CI_AS   --驳回原因
    , submitter_id  BIGINT
    , submitter_no VARCHAR(50) COLLATE Chinese_PRC_CI_AS  --提交人工号
    , submitter_name VARCHAR(200) COLLATE Chinese_PRC_CI_AS    --提交人姓名
    , submitter_time DATETIME   --提交时间
    , total_time  DECIMAL(8,2)
    , total_time_name VARCHAR(20)
  )

  DECLARE @sql VARCHAR(MAX) = '', @sql2 VARCHAR(MAX) = '', @submitDeptId BIGINT


  SELECT @sql = '
    INSERT INTO #result (order_id, order_no, start_date, end_date, people_num, leave_type_id, leave_type_name, remark, 
      status, status_name, last_approver_id, last_approver_name, last_approver_time, returnRemark, submitter_id, submitter_no, submitter_name, submitter_time,
      total_time, total_time_name)
    SELECT DISTINCT order_id, order_no, start_date, end_date, people_num, leave_id, leave_type_name, remark, status, status_name, 
      last_approver_id, last_approver_name, approval_time, return_remark, 
      submitter_id, submitter_no, submitter_name, submitter_time, total_time, dbo.bd_fn_numToMin(total_time)    
    FROM bd_view_leave
    WHERE is_del = 0 '

  SELECT @sql2 = '
    UNION
    SELECT DISTINCT order_id, order_no, start_date, end_date, people_num, leave_id, leave_type_name, remark, status, status_name, 
      last_approver_id, last_approver_name, approval_time, return_remark, 
      submitter_id, submitter_no, submitter_name, submitter_time, total_time, dbo.bd_fn_numToMin(total_time)    
    FROM bd_view_leave
    WHERE is_del = 0 AND submitter_id = ' + CAST(@operateId AS VARCHAR(30))
  
  IF(ISNULL(@dateType, 0) = 1)
  BEGIN
    SELECT @sql += ' AND start_date >=''' + CONVERT(VARCHAR(10), @dateBegin, 120) + ''' AND start_date < ''' + CONVERT(VARCHAR(10), DATEADD(DAY, 1, @dateEnd), 120) + ''' ' 	
    SELECT @sql2 += ' AND start_date >=''' + CONVERT(VARCHAR(10), @dateBegin, 120) + ''' AND start_date < ''' + CONVERT(VARCHAR(10), DATEADD(DAY, 1, @dateEnd), 120) + ''' ' 
  END

  IF(ISNULL(@dateType, 0) = 2)
  BEGIN
    SELECT @sql += ' AND submitter_time >=''' + CONVERT(VARCHAR(10), @dateBegin, 120) + ''' AND submitter_time < ''' + CONVERT(VARCHAR(10), DATEADD(DAY, 1, @dateEnd), 120) + ''' ' 	
    SELECT @sql2 += ' AND submitter_time >=''' + CONVERT(VARCHAR(10), @dateBegin, 120) + ''' AND submitter_time < ''' + CONVERT(VARCHAR(10), DATEADD(DAY, 1, @dateEnd), 120) + ''' ' 	
  END

  IF(ISNULL(@depts, '') <> '')
  BEGIN
  	SELECT @sql += ' AND submitter_dept_id IN (' + @depts + ')'
  END

  --申请人工号、姓名
  IF(ISNULL(@userNameOrNo, '') <> '')
  BEGIN
  	SELECT @sql += ' AND (user_no LIKE ''%' + @userNameOrNo + '%'' OR user_name LIKE ''%' + @userNameOrNo + '%'')'
  	SELECT @sql2 += ' AND (user_no LIKE ''%' + @userNameOrNo + '%'' OR user_name LIKE ''%' + @userNameOrNo + '%'')'
  END

  --提交人工号、姓名
  IF(ISNULL(@applierNameOrNo, '') <> '')
  BEGIN
  	SELECT @sql += ' AND (submitter_no LIKE ''%' + @applierNameOrNo + '%'' OR submitter_name LIKE ''%' + @applierNameOrNo + '%'')'
  	SELECT @sql2 += ' AND (submitter_no LIKE ''%' + @applierNameOrNo + '%'' OR submitter_name LIKE ''%' + @applierNameOrNo + '%'')'
  END

  IF(ISNULL(@orderNo, '') <> '')
  BEGIN
  	SELECT @sql += ' AND order_no LIKE ''%' + @orderNo + '%'' '
  	SELECT @sql2 += ' AND order_no LIKE ''%' + @orderNo + '%'' '
  END


  EXEC(@sql + @sql2)


  SELECT @rowCount = COUNT(1) FROM #result 

  IF(ISNULL(@isPage, 0) = 0)
  BEGIN
  	SELECT * FROM #result ORDER BY swipe_time
  END ELSE
  BEGIN
    ;WITH result AS (
      SELECT *,row_no = ROW_NUMBER() OVER (ORDER BY order_no DESC) FROM #result 
    )
    SELECT * FROM result
    WHERE row_no BETWEEN (@pageNumber - 1) * @pageSize + 1 AND @pageNumber * @pageSize	
  END

  DROP TABLE #result

  RETURN ISNULL(@rowCount, 0)

  SET NOCOUNT OFF;
END